﻿/*********************************************************************************************************************
VanguardDocumentation, View Level1Entities

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'V'
			and o.name = 'Level1Entities'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create view VanguardDocumentation.Level1Entities
		as select ''View Level1Entities is not implemented yet!'' as Error
	';
	execute (@SqlCommand);
end;
GO

alter view VanguardDocumentation.Level1Entities
	/*****************************************************************************************************************
	Lists all entities on level 1 for extended properties.
	
	The column SearchOrder is intended for when the user only gives a name (no type). The order is:
	TABLE, VIEW, FUNCTION, PROCEDURE, TYPE, XML SCHEMA COLLECTION, SYNONYM, AGGREGATE, QUEUE, LOGICAL FILE NAME

	Doesn't support level 1 entities DEFAULT and RULE, since these are deprecated by Microsoft.
	*****************************************************************************************************************/
as
select 1 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TABLE' as Level1Type, t.name collate database_default as Level1Name
from sys.tables as t

union all
select 2 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(v.[schema_id]) collate database_default as Level0Name,
	'VIEW' as Level1Type, v.name collate database_default as Level1Name
from sys.views as v

union all
select 3 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(f.[schema_id]) collate database_default as Level0Name,
	'FUNCTION' as Level1Type, f.name collate database_default as Level1Name
from sys.objects as f
where f.[type]  in ('FN', ' FS', 'FT', 'IF', 'TF')

union all
select 4 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(p.[schema_id]) collate database_default as Level0Name,
	'PROCEDURE' as Level1Type, p.name collate database_default as Level1Name
from sys.procedures as p

union all
select 5 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(t.[schema_id]) collate database_default as Level0Name,
	'TYPE' as Level1Type, t.name collate database_default as Level1Name
from sys.types as t

union all
select 6 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(x.[schema_id]) collate database_default as Level0Name,
	'XML SCHEMA COLLECTION' as Level1Type, x.name collate database_default as Level1Name
from sys.xml_schema_collections as x

union all
select 7 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(s.[schema_id]) collate database_default as Level0Name,
	'SYNONYM' as Level1Type, s.name collate database_default as Level1Name
from sys.synonyms as s

union all
select 8 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(a.[schema_id]) collate database_default as Level0Name,
	'AGGREGATE' as Level1Type, a.name collate database_default as Level1Name
from sys.objects as a
where a.[type] = 'AF'

union all
select 9 as SearchOrder, 'SCHEMA' as Level0Type, schema_name(q.[schema_id]) collate database_default as Level0Name,
	'QUEUE' as Level1Type, q.name collate database_default as Level1Name
from sys.service_queues as q

union all
select 10 as SearchOrder, 'FILEGROUP' as Level0Type, d.name collate database_default as Level0Name,
	'LOGICAL FILE NAME' as Level1Type, f.name collate database_default as Level1Name
from sys.data_spaces as d
	inner join sys.database_files as f on (d.data_space_id = f.data_space_id);
GO




